﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using SCRUM.Core.DAO;
using SCRUM.Core.Exceptions.Database;
using System.Data.SqlClient;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database for user story
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Creates new UserStory.
        /// </summary>
        /// <param name="userStory">The specific UserStory.</param>
        /// <param name="sprintId">Sprint id to add UserStory to.</param>
        public override UserStory CreateUserStory(UserStory userStory, int? sprintId)
        {
            int userStoryID = InsertUserStory(userStory, sprintId);
            return GetUserStoryByID(userStoryID);
        }

        /// <summary>
        /// Inserts the user story.
        /// </summary>
        /// <param name="userStory">The user story.</param>
        /// <param name="sprintId">The sprint id.</param>
        /// <returns>identifier of newly inserted user story</returns>
        private int InsertUserStory(UserStory userStory, int? sprintId)
        {
            try
            {
                DbParameter dbName = CreateParameter("name", userStory.Name);
                DbParameter dbDesc = CreateParameter("description", userStory.Description);
                DbParameter dbState = CreateParameter("userStoryStateId", userStory.State.ID);
                DbParameter dbEst = CreateParameter("estimation", userStory.Estimation);
                DbParameter dbSprintId = CreateParameter("sprintId", sprintId);
                DbParameter dbProgress = CreateParameter("progress", userStory.Progress);
                DbParameter dbPriority = CreateParameter("priority", userStory.Priority);

                connection.Open();
                int data = ExecuteNonQuery("userStory/CreateUserStory", dbName, dbDesc, dbEst, dbSprintId, dbState, dbProgress, dbPriority);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("UserStoryNotCreated");
                }
                return Convert.ToInt32(GetScalar("userStory/GetLastInsertedUserStoryID"));
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("UserStoryNotCreated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the UserStory.
        /// </summary>
        /// <param name="userStory">The specific UserStory.</param>
        /// <param name="sprintId">id of sprint that UserStory is in</param>
        public override void UpdateUserStory(UserStory userStory, int sprintId)
        {
            try
            {
                DbParameter dbId = CreateParameter("id", userStory.ID);
                DbParameter dbName = CreateParameter("name", userStory.Name);
                DbParameter dbDesc = CreateParameter("description", userStory.Description);
                DbParameter dbState = CreateParameter("userStoryStateId", userStory.State.ID);
                DbParameter dbEst = CreateParameter("estimation", userStory.Estimation);
                DbParameter dbSprintId = CreateParameter("sprintId", sprintId);
                DbParameter dbProgress = CreateParameter("progress", userStory.Progress);
                DbParameter dbPriority = CreateParameter("priority", userStory.Priority);

                connection.Open();

                int data = ExecuteNonQuery("userStory/UpdateUserStory", dbId, dbName, dbDesc, dbEst, dbSprintId, dbState, dbProgress, dbPriority);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("UserStory NOT updated!");
                }
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("UserStoryCouldNotBeUpdated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the UserStory.
        /// </summary>
        /// <param name="userStory">The specific UserStory.</param>
        public override void UpdateUserStory(UserStory userStory)
        {
            try
            {
                DbParameter dbId = CreateParameter("id", userStory.ID);
                DbParameter dbName = CreateParameter("name", userStory.Name);
                DbParameter dbDesc = CreateParameter("description", userStory.Description);
                DbParameter dbState = CreateParameter("userStoryStateId", userStory.State.ID);
                DbParameter dbEst = CreateParameter("estimation", userStory.Estimation);
                DbParameter dbProgress = CreateParameter("progress", userStory.Progress);
                DbParameter dbSprintID = CreateParameter("sprintID", userStory.SprintID);
                connection.Open();

                int data = ExecuteNonQuery("userStory/UpdateUserStory2", dbId, dbName, dbDesc, dbEst, dbState, dbSprintID, dbProgress);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("UserStoryToUpdateNotFound");
                }
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("UserStoryCouldNotBeUpdated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the sprint of the user story.
        /// </summary>
        /// <param name="userStoryID">The user story ID.</param>
        /// <param name="sprintID">The sprint ID.</param>
        public override void UpdateUserStorySprint(int userStoryID, int? sprintID)
        {
            DbParameter usID = CreateParameter("userStoryID", userStoryID);
            DbParameter sID;
            if (sprintID == null)
            {
                sID = CreateParameter("sprintID", DBNull.Value);
            }
            else
            {
                sID = CreateParameter("sprintID", sprintID);
            }
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("userStory/UpdateUserStorySprint", usID, sID);
                if (data != 1)
                {
                    throw new Exception("UserStory NOT updated!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the UserStory.
        /// </summary>
        /// <param name="userStoryId">The specific UserStory id.</param>
        public override void DeleteUserStory(int userStoryId)
        {
            try
            {
                DbParameter dbId = CreateParameter("userStoryId", userStoryId);

                connection.Open();

                int data = ExecuteNonQuery("userStory/DeleteUserStory", dbId);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("UserStoryToDeleteNotFound");
                }
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("UserStoryCouldNotBeDeleted");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets the user story from db datareader
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns></returns>
        private UserStory GetUserStory(DbDataReader data)
        {
            return new UserStory(
                GetIntByColumn(data, "ID"), 
                GetIntByColumn(data,"SPRINT_ID"),// != DBNull.Value ? (int?)data["SPRINT_ID"] : null, 
                GetStringByColumn(data, "NAME"), 
                GetStringByColumn(data, "DESCRIPTION"), 
                GetDoubleByColumn(data, "ESTIMATION"), 
                GetIntByColumn(data, "USER_STORY_STATE_ID"), 
                GetIntByColumn(data, "USER_STORY_TYPE_ID"), 
                GetDoubleByColumn(data, "PROGRESS"), 
                GetIntByColumn(data, "PRIORITY"), 
                GetDateByColumn(data, "CHANGED"));
        }

        /// <summary>
        /// Gets all user stories. if any of parameters is -1 it will not be used in filter
        /// </summary>
        /// <param name="projectID">The project ID.</param>
        /// <param name="releaseID">The release ID.</param>
        /// <param name="sprintID">The sprint ID.</param>
        /// <param name="userStoryID">The user story ID.</param>
        /// <param name="bliID">The bli ID.</param>
        /// <returns></returns>
        public override IList<UserStory> GetAllUserStories(int projectID, int releaseID, int sprintID, int userStoryID, int bliID)
        {
            try
            {
                IList<UserStory> userStories = new List<UserStory>();

                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                DbParameter dbReleaseID = CreateParameter("releaseID", releaseID);
                DbParameter dbSprintID = CreateParameter("sprintID", sprintID);
                DbParameter dbBliID = CreateParameter("bliID", bliID);
                DbParameter dbUserStoryID = CreateParameter("userStoryID",userStoryID);

                connection.Open();

                DbDataReader data = GetGrid("userStory/GetAllUserStories", dbProjectID, dbReleaseID, dbSprintID, dbUserStoryID, dbBliID);
                while (data.Read())
                {
                    UserStory userStory = GetUserStory(data);

                    userStories.Add(userStory);
                }
                return userStories;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Get all user stories.
        /// </summary>
        public override IList<UserStory> GetAllUserStories()
        {
            return GetAllUserStories(-1, -1, -1, -1, -1);
        }

        /// <summary>
        /// Get all user stories for project.
        /// <param name="projectID">Project id</param>
        /// </summary>
        public override IList<UserStory> GetAllUserStoriesForProject(int projectID)
        {
            return GetAllUserStories(projectID, -1, -1, -1, -1);
        }

        /// <summary>
        /// Get all user stories for release.
        /// <param name="releaseID">Release id</param>
        /// </summary>
        public override IList<UserStory> GetAllUserStoriesForRelease(int releaseID)
        {
            return GetAllUserStories(-1, releaseID, -1, -1, -1);
        }

        /// <summary>
        /// Get all user stories for sprint.
        /// <param name="sprID">Sprint id</param>
        /// </summary>
        public override IList<UserStory> GetAllUserStoriesForSprint(int sprID)
        {
            return GetAllUserStories(-1, -1, sprID, -1, -1);
        }

        /// <summary>
        ///  Returns user story according to id
        /// </summary>
        /// <param name="userStoryID">id of user story</param>
        /// <returns>user story details</returns>
        public override UserStory GetUserStoryByID(int userStoryID)
        {
            IList<UserStory> userStories = GetAllUserStories(-1,-1,-1,userStoryID,-1);
            if (userStories.Count != 1)
            {
                throw new InconsistentDatabaseException("UserStoryNotFound");
            }
            return userStories[0];
        }

        /// <summary>
        /// Gets the user story by BLI ID.
        /// </summary>
        /// <param name="bliID">ID of the BLI.</param>
        /// <returns>user story containing that BLI</returns>
        public override UserStory GetUserStoryByBliID(int bliID)
        {
            IList<UserStory> userStories = GetAllUserStories(-1, -1, -1, -1, bliID);
            if (userStories.Count != 1)
            {
                throw new InconsistentDatabaseException("UserStoryNotFound");
            }
            return userStories[0];
        }

		/// <summary>
		/// Gets the unassigned stories.
		/// </summary>
		/// <returns>List of unassigned userstories.</returns>
		public override IList<UserStory> GetUnassignedStories()
		{
			List<UserStory> output = new List<UserStory>();
			try
			{
				connection.Open();
				DbDataReader data = GetGrid("userStory/GetUnassignedStories");

				while (data.Read())
				{
                    UserStory userStory = GetUserStory(data);

					output.Add(userStory);
				}
			}
			finally
			{
				connection.Close();
			}
			return output;
		}
	}
}
